package com.huxin.dao;

import com.huxin.pojo.*;
import com.huxin.utlis.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;

public class Userdao {
    private static JdbcTemplate jdbcTemplate=new JdbcTemplate(JDBCUtils.getDataSource());
    public UserInfo userLogin(String username, String password) {
        String sql="select * from user_info where username=? and password=?";
        UserInfo userInfo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<UserInfo>(UserInfo.class), username, password);
        return userInfo;
    }

    public UserInfo selectById(Integer userId) {
        String sql="select * from user_info where aid=?";
        UserInfo userInfo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<UserInfo>(UserInfo.class),userId);
        return userInfo;
    }
    public List<BookHisInfo> selectAllById(Integer aid) {
        String sql="SELECT * FROM book_his_info where aid=?";
        List<BookHisInfo>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class),aid);
        return list;
    }
    public List<BookHisInfo> selectAll() {
        String sql="SELECT * FROM book_his_info";
        List<BookHisInfo>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class));
        return list;
    }

    public int updateById(UserInfo userInfo) {
        String sql="update user_info set name=?,password=?,email=?,phone=?,identity_card=? where aid=?";
        int i = jdbcTemplate.update(sql,userInfo.getName(),userInfo.getPassword(),userInfo.getEmail(),userInfo.getPhone(),userInfo.getIdentity_card(),userInfo.getAid());
        return i;
    }

    public  boolean login(String username, String password) {
        try{
            String sql="select * from user_info where username=? and password=?";
            UserInfo userInfo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<UserInfo>(UserInfo.class), username, password);
            if (userInfo==null){
                return false;
            }
        }catch (Exception e){
            return false;
        }
        return true;
    }

    public int updateservice(String id) {
        String sql="UPDATE book_his_info SET STATUS='2' WHERE bid=?";
        int i = jdbcTemplate.update(sql, id);
        return i;
    }

    public int updateBook(String id) {
        String sql="UPDATE books_info SET num=num+1,STATUS='2' WHERE bid=?";
        int i=jdbcTemplate.update(sql,id);
        return i;
    }

    public static List<UserInfo> selectallreader() {
        String sql="SELECT * FROM user_info";
        List<UserInfo>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<UserInfo>(UserInfo.class));
        return list;
    }
    public static PageBean<UserInfo> selectallreader(Integer page,Integer size) {
        String sql="SELECT * FROM user_info";
        List<UserInfo>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<UserInfo>(UserInfo.class));
        PageBean pageBean=new PageBean<UserInfo>(list,page,size);
        return pageBean;
    }


    public List<BooksInfo> topBooks() {
        String sql="select * from books_info order by card desc limit 0,10";
        List<BooksInfo>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class));

        return list;
    }

    public BooksInfo selectBookdById(Integer bid) {
        String sql="select * from books_info where bid=?";
        BooksInfo booksInfo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class), bid);
        return booksInfo;
    }

    public int insertBorrowbook(BookHisInfo bookHisInfo) {
        String sql="insert into book_his_info" +
                "(aid,bid,card,book_name,admin_name,username,begin_time,end_time,status)" +//
                "values (?,?,?,?,?,?,?,?,?)";//?,?
        int i = jdbcTemplate.update(sql,bookHisInfo.getAid(),bookHisInfo.getBid(),bookHisInfo.getCard(),bookHisInfo.getBook_name(),bookHisInfo.getAdmin_name(),
                bookHisInfo.getUsername(),bookHisInfo.getBegin_time(),bookHisInfo.getEnd_time(),bookHisInfo.getStatus());//
        return i;
    }

    public int decreaseUserNum(Integer aid) {
        String sql="update user_info set max_num=max_num-1 where aid=?";
        int i = jdbcTemplate.update(sql,aid);
        return i;
    }

    public int decreaseBookNum(Integer bid) {
        String sql="update books_info set num=num-1 where bid=?";
        int i = jdbcTemplate.update(sql,bid);
        return i;
    }

    public int addFrequency(Integer userId) {
        String sql="update user_info set frequency=frequency+1 where aid=?";
        int i=jdbcTemplate.update(sql,userId);
        return i;
    }


    public static int Delectservice(String id) {
        String sql="DELETE FROM user_info WHERE aid=?";
        int i = jdbcTemplate.update(sql, id);
        return i;
    }

    public static int updateUser(UserInfo reader) {
        String sql="UPDATE user_info SET username=?,name=?,PASSWORD=?,email=?,phone=?,lend_num=?,max_num=? WHERE aid=?";
        int i=jdbcTemplate.update(sql,reader.getUsername(),reader.getName(),reader.getPassword(),reader.getEmail(),reader.getPhone(),reader.getLend_num(),reader.getMax_num(),reader.getAid());
        return i;
    }

    public static UserInfo findReadById(String aid) {
        UserInfo user = null;
        try{
            String sql = "SELECT * FROM user_info WHERE aid =?";
            user=jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<UserInfo>(UserInfo.class),aid);
        } catch (Exception e){
            e.printStackTrace();
        }
        if(null !=user){
            return  user;
        }else {
            return  null;
        }
    }

    public int addUser(UserInfo userInfo) {
        String sql="insert into user_info(username,name,password,email,phone)" +
                "values (?,?,?,?,?)";
        int i = jdbcTemplate.update(sql,userInfo.getUsername(),userInfo.getName(),userInfo.getPassword(),userInfo.getEmail(),userInfo.getPhone());
        return i;
    }

    public void addUserMaxNum(Integer aid) {
        String sql="update user_info set max_num=max_num+1 where aid=?";
        jdbcTemplate.update(sql,aid);
    }

    public List<UserInfo> selectAllOrderByFrequency() {
        String sql="select * from user_info order by frequency desc limit 0,10";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<UserInfo>(UserInfo.class));
    }

    public List<BooksInfo> selectAllBooks() {
        String sql="select * from books_info";
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class));
    }

    /**
     * 根据书本id查询书籍
     * @param conditionalQueryDTO
     * @return
     */
    public PageBean<BookStorage> booksSConditionalQueryByBid(ConditionalQueryDTO conditionalQueryDTO,Integer page,Integer size) {
        String sql="select * from book_storage where bid like concat('%',?,'%')";
        List<BookStorage> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookStorage>(BookStorage.class),conditionalQueryDTO.getText());
        PageBean<BookStorage> bean=new PageBean<BookStorage>(query,page,size);
        return bean;
    }
    public PageBean<BooksInfo> booksConditionalQueryByBid(ConditionalQueryDTO conditionalQueryDTO,Integer page,Integer size) {
        String sql="select * from books_info where bid like concat('%',?,'%')";
        List<BooksInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class),conditionalQueryDTO.getText());
        PageBean<BooksInfo> bean=new PageBean<BooksInfo>(query,page,size);
        return bean;
    }

    /**
     * 根据作者名查询书籍
     * @param conditionalQueryDTO
     * @return
     */
    public PageBean<BooksInfo> booksConditionalQueryByAuthor(ConditionalQueryDTO conditionalQueryDTO,Integer page,Integer size) {
        String sql="select * from books_info where author like concat('%',?,'%')";
        List<BooksInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class),conditionalQueryDTO.getText());
        PageBean<BooksInfo> bean=new PageBean<BooksInfo>(query,page,size);
        return bean;
    }
    public PageBean<BookStorage> booksConditionalQueryByAuthor1(ConditionalQueryDTO conditionalQueryDTO,Integer page,Integer size) {
        String sql="select * from book_storage where author like concat('%',?,'%')";
        List<BookStorage> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookStorage>(BookStorage.class),conditionalQueryDTO.getText());
        PageBean<BookStorage> bean=new PageBean<BookStorage>(query,page,size);
        return bean;
    }


    public PageBean<BookStorage> booksSConditionalQueryByType(ConditionalQueryDTO conditionalQueryDTO,Integer page,Integer size) {
        String sql="select * from book_storage where type like concat('%',?,'%')";
        List<BookStorage> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookStorage>(BookStorage.class),conditionalQueryDTO.getText());
        PageBean<BookStorage> bean=new PageBean<BookStorage>(query,page,size);
        return bean;
    }
    public PageBean<BooksInfo> booksConditionalQueryByType(ConditionalQueryDTO conditionalQueryDTO,Integer page,Integer size) {
        String sql="select * from books_info where type like concat('%',?,'%')";
        List<BooksInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class),conditionalQueryDTO.getText());
        PageBean<BooksInfo> bean=new PageBean<BooksInfo>(query,page,size);
        return bean;
    }

    public PageBean<BookStorage> booksSConditionalQueryByName(ConditionalQueryDTO conditionalQueryDTO,Integer page,Integer size) {
        String sql="select * from book_storage where name like concat('%',?,'%')";
        List<BookStorage> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookStorage>(BookStorage.class),conditionalQueryDTO.getText());
        PageBean<BookStorage> bean=new PageBean<BookStorage>(query,page,size);
        return bean;
    }
    public PageBean<BooksInfo> booksConditionalQueryByName(ConditionalQueryDTO conditionalQueryDTO,Integer page,Integer size) {
        String sql="select * from books_info where name like concat('%',?,'%')";
        List<BooksInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class),conditionalQueryDTO.getText());
        PageBean<BooksInfo> bean=new PageBean<BooksInfo>(query,page,size);
        return bean;
    }

    /**
     * 根据id分页查询
     * @param userId
     * @param page
     * @param size
     * @return
     */
    public PageBean<BookHisInfo> findAllById(Integer userId, int page, int size) {
        String sql="SELECT * FROM book_his_info where aid=? order by hid desc";
        List<BookHisInfo>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class),userId);
        PageBean<BookHisInfo> bean = new PageBean<BookHisInfo>(list, page, size);
        return bean;
    }

    /**
     * 查询所有书籍借阅记录
     * @param page
     * @param size
     * @return
     */
    public PageBean<BookHisInfo> findAll( int page, int size) {
        String sql="SELECT * FROM book_his_info order by hid desc";
        List<BookHisInfo>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class));
        PageBean<BookHisInfo> bean = new PageBean<BookHisInfo>(list, page, size);
        return bean;
    }

    public PageBean<BooksInfo> findAllBooks(int page, int size) {
        String sql="select * from books_info";
        List<BooksInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class));
        PageBean<BooksInfo> bean=new PageBean<BooksInfo>(query,page,size);
        return bean;
    }
    public PageBean<BookStorage> findAllBooksS(int page, int size) {
        String sql="select * from book_storage";
        List<BookStorage> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookStorage>(BookStorage.class));
        PageBean<BookStorage> bean=new PageBean<BookStorage>(query,page,size);
        return bean;
    }

    public PageBean<BookStorage> findAllBookStorage(int page, int size) {
        String sql="select * from book_storage order by storage_time desc";
        List<BookStorage> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookStorage>(BookStorage.class));
        PageBean<BookStorage> bean=new PageBean<BookStorage>(query,page,size);
        return bean;
    }

    public void addBookHisCard(Integer bid) {
        String sql="update books_info set card=card+1 where bid=?";
        jdbcTemplate.update(sql,bid);
    }

    /**
     * 用户提交问题
     * @param problemFeedback
     * @return
     */
    public int submitProblem(ProblemFeedback problemFeedback) {
        String sql="insert into problem_feedback" +
                "(username,title,problem_page,problem_description,phone,submission_time)" +
                "values (?,?,?,?,?,?)";
        int i = jdbcTemplate.update(sql,problemFeedback.getUsername(),problemFeedback.getTitle(),problemFeedback.getProblem_page(),problemFeedback.getProblem_description()
        ,problemFeedback.getPhone(),problemFeedback.getSubmission_time());
        return i;
    }

    /**
     * 根据用户id查询所反馈的问题
     * @param username
     * @return
     */
    public List<ProblemFeedback> getFeedbackById(String username) {
        String sql="select * from problem_feedback where username=? ";
        List<ProblemFeedback> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<ProblemFeedback>(ProblemFeedback.class),username);
        return query;
    }
    public static int delectbooktype(String id) {
        String sql="DELETE FROM book_type_info WHERE tid=?";
        int i = jdbcTemplate.update(sql, id);
        return i;
    }
    public static int Addtype(BookTypeInfo type) {
        String sql="INSERT INTO book_type_info (NAME) VALUES(?)";
        int i=jdbcTemplate.update(sql,type.getName());
        return  i;
    }

    public static PageBean<BookTypeInfo> selectalltype(Integer page, Integer size) {
        String sql="SELECT * FROM book_type_info";
        List<BookTypeInfo> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookTypeInfo>(BookTypeInfo.class));
        PageBean<BookTypeInfo> bean=new PageBean<BookTypeInfo>(list,page,size);
        return bean;
    }

    public PageBean<UserInfo> selectUserByUsername(String username,Integer page,Integer size) {
        String sql="select * from user_info where username like concat('%',?,'%')";
        List<UserInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<UserInfo>(UserInfo.class),username);
        PageBean<UserInfo> userInfoPageBean = new PageBean<UserInfo>(query,page,size);
        return userInfoPageBean;
    }
    public PageBean<UserInfo> selectUserByname(String name,Integer page,Integer size) {
        String sql="select * from user_info where name like concat('%',?,'%')";
        List<UserInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<UserInfo>(UserInfo.class),name);
        PageBean<UserInfo> userInfoPageBean = new PageBean<UserInfo>(query,page,size);
        return userInfoPageBean;
    }

    public AdminInfo adminLogin(AdminInfo adminInfo) {
        String sql="select * from admin_info where operator_name=? and operator_pass=?";
        try{
            AdminInfo adminInfo1 = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<AdminInfo>(AdminInfo.class),adminInfo.getOperatorName(),adminInfo.getOperatorPass());
            return adminInfo1;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    public List<BookTypeInfo> getBookType() {
        String sql="select * from book_type_info";
        List<BookTypeInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookTypeInfo>(BookTypeInfo.class));
        return query;
    }

    public int addBooks(BooksInfo booksInfo) {
        String sql="insert into books_info(name,author,card,num,press,type,imgpath)values (?,?,?,?,?,?,?)";
        Integer card= Integer.valueOf(booksInfo.getCard());
        int i = jdbcTemplate.update(sql,booksInfo.getName(),booksInfo.getAuthor(),card,booksInfo.getNum(),booksInfo.getPress(),booksInfo.getType(),booksInfo.getImgpath());
        return i;
    }

    public int deleteBookdsById(String ids) {
        String sql="delete from books_info where bid in ("+ids+")";
        int i = jdbcTemplate.update(sql);
        return i;
    }

    public BookTypeInfo selectBooksTypeName(String id) {
        String sql="select * from book_type_info where tid=?";
            BookTypeInfo bookTypeInfo = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<BookTypeInfo>(BookTypeInfo.class),id);
            return bookTypeInfo;
    }

    public List<BooksInfo> selectBooksByTpyeName(String typeName) {
        String sql="select * from books_info where type=?";
        try{
            List<BooksInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class),typeName);
            return query;
        }catch (Exception e){
            return null;
        }

    }

    public List<ProblemFeedback> getAllProblem() {
        String sql="SELECT * from problem_feedback order by status asc";
        List<ProblemFeedback> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<ProblemFeedback>(ProblemFeedback.class));
        return query;
    }

    public int updateProblemStatus(String pid) {
        String sql="update problem_feedback set status=1 where pid=?";
        int i = jdbcTemplate.update(sql,pid);
        return i;
    }

    public int deleteProblemById(String pid) {
        String sql="delete from problem_feedback where pid=?";
        int i = jdbcTemplate.update(sql,pid);
        return i;
    }

    public int updateAdminPwd(AdminInfo adminInfo) {
        String sql="update admin_info set operator_pass=? where operator_name=? ";
        int i = jdbcTemplate.update(sql,adminInfo.getOperatorPass(),adminInfo.getOperatorName());
        return i;
    }

    public PageBean<BookHisInfo> findAllbyBid(ConditionalQueryDTO conditionalQueryDTO,Integer page,Integer size) {
        String sql="select * from book_his_info where bid=? order by begin_time desc";
        List<BookHisInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class),conditionalQueryDTO.getText());
        PageBean<BookHisInfo> bean = new PageBean<BookHisInfo>(query,page,size);
        return bean;
    }
    public PageBean<BookHisInfo> findAllbyBid(ConditionalQueryDTO conditionalQueryDTO,Integer aid) {
        String sql="select * from book_his_info where bid=? and aid=?";
        List<BookHisInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class),conditionalQueryDTO.getText(),aid);
        PageBean<BookHisInfo> bean = new PageBean<BookHisInfo>(query,conditionalQueryDTO.getPage(),conditionalQueryDTO.getSize());
        return bean;
    }
    public PageBean<BookHisInfo> findAllbyUserName(ConditionalQueryDTO conditionalQueryDTO, int page, int size) {
        String sql="select * from book_his_info where username like concat('%',?,'%') order by begin_time desc";
        List<BookHisInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class),conditionalQueryDTO.getText());
        PageBean<BookHisInfo> bean = new PageBean<BookHisInfo>(query,page,size);
        return bean;
    }

    public PageBean<BookHisInfo> findAllbyUserName(ConditionalQueryDTO conditionalQueryDTO, Integer aid) {
        String sql="select * from book_his_info where author like concat('%',?,'%') and aid=?";
        List<BookHisInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class),conditionalQueryDTO.getText(),aid);
        PageBean<BookHisInfo> bean = new PageBean<BookHisInfo>(query,conditionalQueryDTO.getPage(),conditionalQueryDTO.getSize());
        return bean;
    }

    public PageBean<BookHisInfo> findAllbyBookName(ConditionalQueryDTO conditionalQueryDTO, int page, int size) {
        String sql="select * from book_his_info where book_name like concat('%',?,'%') order by begin_time desc";
        List<BookHisInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class),conditionalQueryDTO.getText());
        PageBean<BookHisInfo> bean = new PageBean<BookHisInfo>(query,page,size);
        return bean;
    }
    public PageBean<BookHisInfo> findAllbyBookName(ConditionalQueryDTO conditionalQueryDTO, Integer aid) {
        String sql="select * from book_his_info where book_name like concat('%',?,'%') and aid=?";
        List<BookHisInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class),conditionalQueryDTO.getText(),aid);
        PageBean<BookHisInfo> bean = new PageBean<BookHisInfo>(query,conditionalQueryDTO.getPage(),conditionalQueryDTO.getSize());
        return bean;
    }

    public int updateBookById(BooksInfo booksInfo) {
        String sql="update books_info set name=?,author=?,num=?,press=?,type=? where bid=?";
        int i = jdbcTemplate.update(sql,booksInfo.getName(),booksInfo.getAuthor(),booksInfo.getNum(),booksInfo.getPress(),booksInfo.getType(),booksInfo.getBid());
        return i;
    }

    public BookHisInfo findBookHisInfoById(String hid) {
        String sql="select * from book_his_info where hid=?";
        BookHisInfo bookHisInfo = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class),hid);
        return bookHisInfo;
    }

    public int deleteBookHisInfoByHid(String hid) {
        String sql="delete from book_his_info where hid=?";
        int i = jdbcTemplate.update(sql,hid);
        return i;
    }

    public int updateBookHisInfoByHid(BookHisInfo bookHisInfo) {
        String sql="update book_his_info set end_time=?,status=? where hid=?";
        int i = jdbcTemplate.update(sql,bookHisInfo.getEnd_time(),bookHisInfo.getStatus(),bookHisInfo.getHid());
        return i;
    }

    public PageBean<Bookreservation> selectallById(Integer userId, int page, int size) {
        String sql="SELECT * FROM book_reservation where aid=? order by rid desc";
        List<Bookreservation>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class),userId);
        PageBean<Bookreservation> bean = new PageBean<Bookreservation>(list, page, size);
        return bean;
    }

    public PageBean<Bookreservation> findById(Integer userId, int page, int size) {
        String sql="SELECT * FROM book_reservation where aid=? order by rid desc";
        List<Bookreservation>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class),userId);
        PageBean<Bookreservation> bean = new PageBean<Bookreservation>(list, page, size);
        return bean;
    }

    public PageBean<Bookreservation> findbyBid(ConditionalQueryDTO conditionalQueryDTO, Integer userId) {
        String sql="select * from book_reservation where bid=? and aid=?";
        List<Bookreservation> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class),conditionalQueryDTO.getText(),userId);
        PageBean<Bookreservation> bean = new PageBean<Bookreservation>(query,conditionalQueryDTO.getPage(),conditionalQueryDTO.getSize());
        return bean;
    }

    public PageBean<Bookreservation> findbyBookName(ConditionalQueryDTO conditionalQueryDTO, Integer userId) {
        String sql="select * from book_reservation where book_name like concat('%',?,'%') and aid=?";
        List<Bookreservation> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class),conditionalQueryDTO.getText(),userId);
        PageBean<Bookreservation> bean = new PageBean<Bookreservation>(query,conditionalQueryDTO.getPage(),conditionalQueryDTO.getSize());
        return bean;
    }

    public PageBean<Bookreservation> selectallreservation(int page, int size) {
        String sql="SELECT * FROM book_reservation order by rid desc";
        List<Bookreservation>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class));
        PageBean<Bookreservation> bean = new PageBean<Bookreservation>(list, page, size);
        return bean;
    }

    public PageBean<Bookreservation> findAllreservation(int page, int size) {
        String sql="SELECT * FROM book_reservation order by rid desc";
        List<Bookreservation>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class));
        PageBean<Bookreservation> bean = new PageBean<Bookreservation>(list, page, size);
        return bean;
    }

    public PageBean<Bookreservation> findAllbyBookId(ConditionalQueryDTO conditionalQueryDTO) {
        String sql="select * from book_reservation where bid=?";
        List<Bookreservation> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class),conditionalQueryDTO.getText());
        PageBean<Bookreservation> bean = new PageBean<Bookreservation>(query,conditionalQueryDTO.getPage(),conditionalQueryDTO.getSize());
        return bean;
    }

    public PageBean<Bookreservation> findAllbyBook(ConditionalQueryDTO conditionalQueryDTO) {
        String sql="select * from book_reservation where book_name like concat('%',?,'%')";
        List<Bookreservation> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class),conditionalQueryDTO.getText());
        PageBean<Bookreservation> bean = new PageBean<Bookreservation>(query,conditionalQueryDTO.getPage(),conditionalQueryDTO.getSize());
        return bean;
    }

    public PageBean<Bookreservation> findbyUserName(ConditionalQueryDTO conditionalQueryDTO) {
        String sql="select * from book_reservation where username=?";
        List<Bookreservation> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class),conditionalQueryDTO.getText());
        PageBean<Bookreservation> bean = new PageBean<Bookreservation>(query,conditionalQueryDTO.getPage(),conditionalQueryDTO.getSize());
        return bean;
    }

    public List<Bookreservation> selectAllreservation() {
        String sql="SELECT * FROM book_reservation";
        List<Bookreservation>list=jdbcTemplate.query(sql,new BeanPropertyRowMapper<Bookreservation>(Bookreservation.class));
        return list;
    }

    public int insertReservebook(Bookreservation bookreservation) {
        String sql="insert into book_reservation" +
                "(aid,bid,card,book_name,admin_name,username,begin_time,end_time,status)" +//
                "values (?,?,?,?,?,?,?,?,?)";//?,?
        int i = jdbcTemplate.update(sql,bookreservation.getAid(),bookreservation.getBid(),bookreservation.getCard(),bookreservation.getBook_name(),bookreservation.getAdmin_name(),
                bookreservation.getUsername(),bookreservation.getBegin_time(),bookreservation.getEnd_time(),bookreservation.getStatus());//
        return i;
    }

    public int delectereservation(int bid) {
        String sql="DELETE FROM book_reservation WHERE bid=?";
        int i = jdbcTemplate.update(sql, bid);
        return i;
    }

    public int addhisinfo(BookHisInfo bookHisInfo) {
        String sql="insert into book_his_info" +
                "(aid,bid,card,book_name,admin_name,username,begin_time,end_time,status)" +//
                "values (?,?,?,?,?,?,?,?,?)";//?,?
        int i = jdbcTemplate.update(sql,bookHisInfo.getAid(),bookHisInfo.getBid(),bookHisInfo.getCard(),bookHisInfo.getBook_name(),bookHisInfo.getAdmin_name(),
                bookHisInfo.getUsername(),bookHisInfo.getBegin_time(),bookHisInfo.getEnd_time(),bookHisInfo.getStatus());//
        return i;
    }

    public int delectbookinfo(Integer bid) {
        String sql="update books_info set num=num-1 where bid=?";
        int i=jdbcTemplate.update(sql,bid);
        return i;
    }

    public int cancelbooks(Integer bid) {
        String sql="update books_info set num=num+1 where bid=?";
        int i=jdbcTemplate.update(sql,bid);
        return i;
    }
    public int updateBookHisInfoEndTimeByHid(BookHisInfo bookHisInfo) {
        String sql="update book_his_info set end_time=? where hid=?";
        int i = jdbcTemplate.update(sql,bookHisInfo.getEnd_time(),bookHisInfo.getHid());
        return i;
    }

    public int updateUserStatus(String id) {
        String sql="update user_info set status=2 where aid=?";
        int i = jdbcTemplate.update(sql,id);
        return i;
    }
    public int updateUserStatusTure(String id) {
        String sql="update user_info set status=1 where aid=?";
        int i = jdbcTemplate.update(sql,id);
        return i;
    }

    public int addBookNum(Integer bid) {
        String sql="update books_info set num=num+1 where bid=?";
        int i = jdbcTemplate.update(sql,bid);
        return i;
    }

    public int AdminAgreeUser(String aid) {
        String sql="update user_info set status=1 where aid=?";
        int i = jdbcTemplate.update(sql,aid);
        return i;
    }

    public int AdminAgreeUserAll() {
        String sql="update user_info set status=1 where status=0 ";
        int i = jdbcTemplate.update(sql);
        return i;
    }

    /**
     * 根据书名和作者查询书籍
     * @param booksInfo
     * @return
     */
    public BooksInfo findBookByNameAndAuthor(BooksInfo booksInfo) {
        try{
            String sql="select * from books_info where name=? and author=?";
            BooksInfo booksInfo1 = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<BooksInfo>(BooksInfo.class),booksInfo.getName(),booksInfo.getAuthor());
            if (booksInfo1!=null){
                return booksInfo1;
            }else {
                return null;
            }
        }catch (Exception e){
            return null;
        }
    }

    public int addBookNumByNameAndAuthor(BooksInfo booksInfo) {
        String sql="update books_info set num=num+? where name=? and author=?";
        int i = jdbcTemplate.update(sql,booksInfo.getNum(),booksInfo.getName(),booksInfo.getAuthor());
        return i;
    }

    public int addBookStorage(BookStorage bookStorage) {
        String sql="insert into book_storage(name,bid,author,num,type,imgpath,storage_time,press)" +
                "values (?,?,?,?,?,?,?,?)";
        int i = jdbcTemplate.update(sql,bookStorage.getName(),bookStorage.getBid(),bookStorage.getAuthor()
                ,bookStorage.getNum(),bookStorage.getType(),bookStorage.getImgpath()
                ,bookStorage.getStorage_time(),bookStorage.getPress());
        return i;
    }

    public PageBean<BookHisInfo> findAllNotReturn(Integer page,Integer size) {
        String sql="select * from book_his_info where status=1";
        List<BookHisInfo> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BookHisInfo>(BookHisInfo.class));
        PageBean<BookHisInfo> pageBean=new PageBean<BookHisInfo>(query,page,size);
        return pageBean;
    }

    public UserInfo getUserName(UserInfo userInfo) {
        String sql="select * from user_info where username=? and password=?";
        UserInfo userInfo1 = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<UserInfo>(UserInfo.class),userInfo.getUsername(),userInfo.getPassword());
        return userInfo1;
    }

    public int deleteUserByAid(String aid) {
        String sql="delete from user_info where aid=?";
        int i = jdbcTemplate.update(sql,aid);
        return i;
    }
}
